package com.xpc.mapper;

import com.xpc.pojo.qin.DepartmentMembersInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author Qinkuer
 * @version 1.0.0
 * @ProjectName UniversityGradeAdministrationSystem
 * @ClassName DepartmentMembersInfoMapper.java
 * @Description TODO
 * @createTime 2022年05月26日 17:15:00
 */
@Mapper
public interface DepartmentMembersInfoMapper {

    /**
        * @description 通过部门编号获取部门成员信息
        * @param departmentId 部门id,即部门编号
        * @return  java.util.List<com.xpc.pojo.qin.DepartmentMembersInfo>
        * @author Qinkuer
        * @Date  2022/5/26
        */
    @Select("select mcg.* , a.account_id as accountId    from \n" +
            "\t(select mcg.*, u.name   from \n" +
            "\t\t(select mcg.* ,m.name as major \t\tfrom\n" +
            "\t\t\t(select mcg.*,c.name as className from\n" +
            "\t\t\t\t(select mcg.*,s.class_id,s.dorm as dormitoryNo,s.phone ,s.major_id from\n" +
            "\t\t\t\t\t(select mcg.id as committeeGradeId,mcg.d_c_g_id as departmentId,mcg.user_id as userId,mcg.position_id as positionId,p.name as positionName from \n" +
            "\t\t\t\t\tmember_committee_grade mcg left join `position` p on mcg.position_id = p.id where mcg.d_c_g_id = #{s_departmentId}  )as mcg\n" +
            "\t\t\t\tleft join student s on mcg.userId = s.user_id ) as mcg \n" +
            "\t\t\tleft join class c on mcg.class_id = c.id )as mcg \n" +
            "\t\tleft join major m on mcg.major_id = m.id )as mcg \n" +
            "\tleft join `user` u on mcg.userId = u.user_id )as mcg\n" +
            "left join account a on mcg.userId = a.user_id ")
    List<DepartmentMembersInfo> getMemberInforamtionByDepartmentId(@Param("s_departmentId") Integer departmentId);
//    select mcg.* , a.account_id as accountId    from
//            (select mcg.*, u.name   from
//		(select mcg.* ,m.name as major 		from
//                     (select mcg.*,c.name as className from
//                     (select mcg.*,s.class_id,s.dorm as dormitoryNo,s.phone ,s.major_id from
//					(select mcg.id as committeeGrageId,mcg.d_c_g_id as departmentId,mcg.user_id as userId,mcg.position_id as positionId,p.name as positionName from
//                     member_committee_grade mcg left join `position` p on mcg.position_id = p.id where mcg.d_c_g_id = 1  )as mcg
//    left join student s on mcg.userId = s.user_id ) as mcg
//    left join class c on mcg.class_id = c.id )as mcg
//    left join major m on mcg.major_id = m.id )as mcg
//    left join `user` u on mcg.userId = u.user_id )as mcg
//    left join account a on mcg.userId = a.user_id

}
